package ibms_rcp.dao;

import ibms_rcp.db.DataBaseManager;
import ibms_rcp.model.BillLog;
import ibms_rcp.model.PayLog;
import ibms_rcp.util.SqlUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

/*
 * 将业主需要交费的物业费明细保存在账单表中，用于显示给用户看，
 * 若用户马上交费的话，及时记录到消费记录表，并更新账单表的支付状态
 * 若用户不缴费的话，不记录消费记录表
 */
public class PropertyManager {
	
	
	//当用户选择好房间号，以及选择缴费月数之后，就可以调用本方法，获得用户这次交物业费的相信信息
	//包含可以显示在页面上的，开始日期，截止日期，缴费金额等。
	public static BillLog showProperty(Long room_moi_id,String beginDate,int month){
		BillLog bLog = new BillLog();
		Long company_moi_id = SqlUtil.getCompanyMoi_id(room_moi_id);
		String company_name = SqlUtil.getCompanyNamebyCompanyMoi_id(company_moi_id);
		//String beginDate = getBeginDateString(room_moi_id);
		String endDate = getEndDateString(beginDate, month);
		if (company_moi_id != null && company_name != null) {
			bLog = showOneRoomProperty(company_moi_id,company_name,room_moi_id,beginDate,endDate,month);
		}
		return bLog;
	}
	/*
	 * 从一个房间号moi_id,查找账单表，找到给房间号 最后一次缴费的记录，获得这次记录的缴费截止日期
	 * 以这个日期作为本次缴费的开始日期，并且返回给界面
	 */
	public static String getBeginDateString(Long room_moi_id){
		String begin = null;
		Connection conn = DataBaseManager.getConnection();
		try {
			conn.setAutoCommit(false);
			String sqlString = "select newvalue from m2mnap_estate_bill_log " +
					" where room_moi_id =? and bill_type =? " +
					"order by bill_time desc";
			PreparedStatement pstmt = conn.prepareStatement(sqlString);
			pstmt.setLong(1, room_moi_id);
			pstmt.setString(2, "物业费");
			ResultSet rs = pstmt.executeQuery();
			//如果账单表里有该房间的物业费记录，则使用这个记录的截止日期
			if(rs.next()){
				begin = rs.getString("newvalue");
			}
			//若果没有记录，表明该房间是第一次交物业费，这个开始日期为该房间登记时的入驻日期
			else{
				sqlString = "select new_value from m2mnap_moi_attribute where moi_id = ? and moc_attribute_code =?";
				pstmt = conn.prepareStatement(sqlString);
				pstmt.setLong(1, room_moi_id);
				pstmt.setString(2, "beginHireDate");
				rs = pstmt.executeQuery();
				if(rs.next()){
					begin = rs.getString("new_value");
				}
			}
			
			pstmt.close();
			conn.commit();

		} catch (SQLException e) {
			try {
				// 回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		return begin;
	}
	//根据开始日期和缴费月数，获得截止日期
	public static String getEndDateString(String begin,int mon){
		String end = null;
		int y = Integer.valueOf(begin.split("-")[0]);
		int mo = Integer.valueOf(begin.split("-")[1]);
		int d = Integer.valueOf(begin.split("-")[2]);
		
		int m = mo+mon;
		String mm = null;
		
		if(m > 12){
			y+=1;
			m-=12;
		}
		if(m < 10){
			mm = "0"+m;
		}else {
			mm = Integer.toString(m);
		}
		end = y+"-"+mm+"-"+d;
		return end;
	}

	//计算房间物业费的单价*面积的值
	public static double getAreaPricebyRoom_moi_id(Long room_moi_id){
		double AreaPrice = 0;
		double area = 0;
		double price = 0;
		Connection conn = DataBaseManager.getConnection();
		try {
			conn.setAutoCommit(false);
			String sqlString = "select new_value from m2mnap_moi_attribute " +
					" where moi_id =? and moc_attribute_code =? ";
			PreparedStatement pstmt = conn.prepareStatement(sqlString);
			pstmt.setLong(1, room_moi_id);
			pstmt.setString(2, "area");
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				area = Double.valueOf(rs.getString("new_value"));
				System.out.println("Area:"+area);
			}
			
			pstmt.setString(2, "price");
			rs = pstmt.executeQuery();
			if(rs.next()){
				price = Double.valueOf(rs.getString("new_value"));
				System.out.println("price:"+price);
			}
			if(area != 0.0 && price !=0.0){
				AreaPrice = area*price;
			}
			
			pstmt.close();
			conn.commit();

		} catch (SQLException e) {
			try {
				// 回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		return AreaPrice;
	}
	
	
	//显示给用户一个房间的物业费信息
	public static BillLog showOneRoomProperty(Long company_moi_id,String compnay_name,Long room_moi_id,
			String beginDate,String endDate,int month){
		//用于显示给业主看的需要交的物业费的信息
		BillLog billLog = new BillLog();
		String bu = SqlUtil.getBuFlRoInfo(room_moi_id)[0];
		String fl = SqlUtil.getBuFlRoInfo(room_moi_id)[1];
		String ro = SqlUtil.getBuFlRoInfo(room_moi_id)[2];
		billLog.setCompany_moi_id(company_moi_id);
		billLog.setCompany_name(compnay_name);
		billLog.setRoom_moi_id(room_moi_id);
		billLog.setBu(bu);
		billLog.setFl(fl);
		billLog.setRo(ro);
		billLog.setBilltime(SqlUtil.getTimeStamp());
		billLog.setBill_type("物业费");
		billLog.setOldvalue(beginDate);
		billLog.setNowvalue(endDate);
		billLog.setCostvalue(Integer.toString(month));
		
		double areaPrice = getAreaPricebyRoom_moi_id(room_moi_id);
		System.out.println("areaPrice:"+areaPrice);
		billLog.setBill_point(month*areaPrice);
		return billLog;
	}
	

	//当确认缴物业费时，保存对应房间的记录，包含账单记录表和消费记录表。两个表同时更新
	public static void saveOneRoomLogs(BillLog bLog) {
		bLog = saveOneinBillLogs(bLog);
		saveOneinPayLogs(bLog);
	}
	//保存物业费到账单表，为了在后面的保存到消费记录表中使用bill_id，所以此方法返回含有bill_id 的 BillLog
	public static BillLog saveOneinBillLogs(BillLog bl){
		Connection conn = DataBaseManager.getConnection();
		try {
			conn.setAutoCommit(false);
			String sqlString = "insert into m2mnap_estate_bill_log"
					+ "(id,company_moi_id,company_name,room_moi_id,buildingid,floorid,"
					+ "roomid,bill_time,bill_type,oldvalue,newvalue,costvalue,bill_point,dealflag) values "
					+ "( nextval('m2mnap_estate_bill_log_seq'),?,?,?,?,?,?,?,?,?,?,?,?,?)";
			PreparedStatement pstmt = conn.prepareStatement(sqlString);
			pstmt.setLong(1, bl.getCompany_moi_id());
			pstmt.setString(2, bl.getCompany_name());
			pstmt.setLong(3, bl.getRoom_moi_id());
			pstmt.setString(4, bl.getBu());
			pstmt.setString(5, bl.getFl());
			pstmt.setString(6, bl.getRo());
			pstmt.setTimestamp(7, bl.getBilltime());
			pstmt.setString(8, bl.getBill_type());
			pstmt.setString(9, bl.getOldvalue());
			pstmt.setString(10, bl.getNowvalue());
			pstmt.setString(11, bl.getCostvalue());
			pstmt.setDouble(12, bl.getBill_point());
			bl.setDealflag(true);
			pstmt.setBoolean(13, bl.isDealflag());
			
			if(pstmt.executeUpdate() == 1){
				System.out.println("物业费-账单记录成功");
				sqlString = "select id from m2mnap_estate_bill_log where company_moi_id = ? and room_moi_id =? " +
						" and bill_time = ?";
				pstmt = conn.prepareStatement(sqlString);
				pstmt.setLong(1, bl.getCompany_moi_id());
				pstmt.setLong(2, bl.getRoom_moi_id());
				pstmt.setTimestamp(3, bl.getBilltime());
				ResultSet rs = pstmt.executeQuery();
				if(rs.next()){
					bl.setId(rs.getLong("id"));
					System.out.println("bill_id:"+bl.getId());
				}else {
					;
				}
			}
			pstmt.close();
			conn.commit();
			
		} catch (SQLException e) {
			try {
				// 回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		
		return bl;
	}
	//在将物业费保存在账单表中后，保存到消费记录表中。
	public static void saveOneinPayLogs(BillLog bl){
		PayLog pLog = new PayLog();
		pLog.setCompany_id(bl.getCompany_moi_id());
		pLog.setCompany_name(bl.getCompany_name());
		pLog.setBill_id(bl.getId());
		pLog.setPay_time(SqlUtil.getTimeStamp());
		pLog.setBalance(SqlUtil.getBalancebyCompanyMoi_id(bl.getCompany_moi_id()));
		//
		Connection conn = DataBaseManager.getConnection();
		try {
			conn.setAutoCommit(false);
			String sqlString = "insert into m2mnap_estate_deal_log(id,company_moi_id,company_name,bill_id,pay_time,point) "
					+ " values(nextval('m2mnap_estate_deal_log_seq'),?,?,?,?,?)";
			PreparedStatement pstmt = conn.prepareStatement(sqlString);
			pstmt.setLong(1, pLog.getCompany_id());
			pstmt.setString(2, pLog.getCompany_name());
			pstmt.setLong(3, pLog.getBill_id());
			pstmt.setTimestamp(4, SqlUtil.getTimeStamp());
			//由于物业费，现场缴费，现场结算，所以对于已开通预收费的公司的余额没有什么影响
			pstmt.setDouble(5, pLog.getBalance());
			if (pstmt.executeUpdate() == 1) {
				// 记录消费记录表成功,由于已经将账单表中的物业费付款表示更新为true，所以此处不用更新账单表。
				System.out.println("记录消费记录表成功");
			}else {
				System.out.println("记录消费记录表失败");
			}
			
			pstmt.close();
			conn.commit();
		}catch (SQLException e) {
			try {
				// 回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
	}
	
	public static void main(String args[]){
		String begin = getBeginDateString(4823L);
		//saveOneRoomLogs(showProperty(4823L, begin, 6));
		//System.out.println(begin);
	}
	
}
